﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Security;
using System.Text;
using System.Web;
namespace webnew.DBUtility
{
    /// <summary>
    /// 数据库访问基类
    /// </summary>
    public class SqlHelper: IDisposable
    {

        

        /// <summary>
        /// 重载一：默认key为connectionstring的value为数据库连接字符串
        /// </summary>
        public SqlHelper()
        {
            try
            {
                //  _connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                _connStr = FindConnPwd(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            }
            catch
            {
                throw new Exception("找不到该数据库连接,请检查数据库连接字符是否正确");
            }
        }
        /// <summary>
        /// 重载二:设定一个数据库连接字符串的KEY值
        /// </summary>
        /// <param name="connstr">KEY值</param>
        public SqlHelper(string connstr)
        {
            try
            {
                //_connStr = System.Configuration.ConfigurationManager.ConnectionStrings[connstr].ConnectionString;
                _connStr = FindConnPwd(System.Configuration.ConfigurationManager.ConnectionStrings[connstr].ConnectionString);
            }
            catch
            {
                throw new Exception("找不到该数据库连接,请检查数据库连接字符是否正确");
            }
        }

        #region 变量
        private string _connStr = string.Empty;
        private bool _tranFlag = false;
        private SqlTransaction _tran = null;
        private SqlConnection _conn = null;
        #endregion

        #region 属性
        /// <summary>
        /// 是否执行事务
        /// </summary>
        public bool TranFlag
        {
            get { return _tranFlag; }
            set { _tranFlag = value; }
        }

        /// <summary>
        /// 获取当前执行的事务
        /// </summary>
        public SqlTransaction Tran
        {
            get { return _tran; }
            set { _tran = value; }
        }
        #endregion

       

        #region 私有方法
        /// <summary>
        /// 初始化一个SqlCommand对象
        /// </summary>
        private void CreateSqlCommand(SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            if (TranFlag)
            {
                cmd.Connection = _tran.Connection;
                cmd.Transaction = _tran;
            }
            else
            {
                try
                {
                    _conn = new SqlConnection(_connStr);                    
                    _conn.Open();
                    cmd.Connection = _conn;
                }
                catch (SqlException ex) { throw ex; }
            }
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            if (!object.Equals(cmdParas, null)) { cmd.Parameters.AddRange(cmdParas); }
        }
        /// <summary>
        /// 初始化并返回一个SqlParameter对象
        /// </summary>
        public SqlParameter CreateSqlParameter(string paraName,DbType paraType,int paraSize,ParameterDirection paraDirection,object paraValue)
        {
            SqlParameter p = new SqlParameter();
            p.ParameterName = paraName;
            p.DbType =  paraType;
            if (paraSize > 0) p.Size = paraSize;
            p.Direction = paraDirection;
            p.Value = paraValue;
            return p;
        }
        /// <summary>
        /// 初始化并返回一个输入型SqlParameter对象
        /// </summary>
        public SqlParameter CreateInSqlParameter(string paraName, DbType paraType, int paraSize, object paraValue)
        {
            return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Input, paraValue);
        }
        /// <summary>
        /// 初始化并返回一个输入型SqlParameter对象
        /// </summary>
        public SqlParameter CreateInSqlParameter(string paraName, DbType paraType, object paraValue)
        {
            return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Input, paraValue);
        }
        /// <summary>
        /// 初始化并返回一个输出型SqlParameter对象
        /// </summary>
        public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, int paraSize, object paraValue)
        {
            return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Output, paraValue);
        }
        /// <summary>
        /// 初始化并返回一个输出型SqlParameter对象
        /// </summary>
        public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, object paraValue)
        {
            return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Output, paraValue);
        }
        /// <summary>
        /// 手动关闭数据库
        /// </summary>
        public void CloseDB()
        {
            if (_conn.State != ConnectionState.Closed && !TranFlag)
            {
                _conn.Close();
                _conn.Dispose();
            }
        }
        #endregion

        #region 公有方法
        /// <summary>
        /// 执行一条带参数的SQL语句,返回受影响的数据库行数
        /// </summary>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            int result = -1;
            using (SqlCommand Cmd = new SqlCommand())
            {
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                result =  Cmd.ExecuteNonQuery();
                Cmd.Parameters.Clear();
                CloseDB();
                return result;
            }
        }
       
        /// <summary>
        /// 执行一条SQL语句，返回受影响的数据库行数
        /// </summary>
        public int ExecuteNonQuery(string cmdText)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, null);
        }
        
        /// <summary>
        /// 执行一条带参数的SQL语句,返回一个数据集
        /// </summary>
        public DataSet ExecuteQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            using (SqlCommand Cmd = new SqlCommand())
            {
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds, "tbl");
                    Cmd.Parameters.Clear();
                    CloseDB();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行一条带参数的SQL语句,返回一个数据集(可修改DataTable名)
        /// </summary>
        public DataSet ExecuteQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParas,string strTableName)
        {
            using (SqlCommand Cmd = new SqlCommand())
            {
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds, strTableName);
                    Cmd.Parameters.Clear();
                    CloseDB();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行一条带参数的SQL语句,返回一个数据集(传入ds,DataTable名)
        /// </summary>
        public DataSet ExecuteQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParas,DataSet ds, string strTableName)
        {
            using (SqlCommand Cmd = new SqlCommand())
            {
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                {
                    //DataSet ds = new DataSet();
                    da.Fill(ds, strTableName);
                    Cmd.Parameters.Clear();
                    CloseDB();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行一条SQL语句，返回一个数据集
        /// </summary>
        public DataSet ExecuteQuery(string cmdText)
        {
            return ExecuteQuery(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 执行一条带参数的SQL语句,返回第一行第一列的值
        /// </summary>
        public object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            object result;
            using (SqlCommand Cmd = new SqlCommand())
            {
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                result =  Cmd.ExecuteScalar();
                Cmd.Parameters.Clear();
                CloseDB();
                return result;
            }
        }

        /// <summary>
        /// 将DataTable数据导入Sqlserver
        /// </summary>
        /// <param name="souredt">数据集</param>
        /// <param name="strTableName">导入的表名</param>
        /// <param name="lstFiled">数据集字段索引与数据库字段索引映射数组</param>
        /// <returns></returns>
        public bool SqlBulkCopyInsert(DataTable souredt, string strTableName, List<string> lstFiled)
        {
            try
            {
                if (souredt != null && souredt.Rows.Count != 0)
                {

                    //数据批量导入sqlserver,创建实例
                    System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(_connStr);
                    //目标数据库表名
                    sqlbulk.DestinationTableName = strTableName;
                    //数据集字段索引与数据库字段索引映射       
                    for (int i = 0; i < lstFiled.Count; i++)
                    {
                        string[] strCouName = lstFiled[i].Split('|');
                        sqlbulk.ColumnMappings.Add(strCouName[0], strCouName[1]);
                        //sqlbulk.ColumnMappings.Add(2, "Volume");
                    }
                    //导入
                    sqlbulk.WriteToServer(souredt);
                    sqlbulk.Close();
                    return true;
                }
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// DataSet 批量更新
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool DataSetUpdate(DataSet ds, string strSql)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(strSql,_connStr);
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Update(ds.Tables[0]);
                ds.AcceptChanges();
                return true;
            }
            catch
            {
                return false;
            }      
        }

      
        //public object ExecuteByCommandBuilder(CommandType cmdType, string cmdText, SqlParameter[] cmdParas, DataTable sourceDable)
        //{
        //    object result;
        //    using (SqlCommand Cmd = new SqlCommand())
        //    {               
        //        CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
        //        SqlDataAdapter da = new SqlDataAdapter();
        //        da.SelectCommand = Cmd;
        //        SqlCommandBuilder sqlcommandbuilder = new SqlCommandBuilder(da);
        //        result= da.Update(sourceDable);
        //        Cmd.Parameters.Clear();
        //        CloseDB();
               
        //        return result;
        //    }
        //} 

        /// <summary>
        /// 执行一条SQL语句，返回第一行第一列的值
        /// </summary>
        public object ExecuteScalar(string cmdText)
        {
            return ExecuteScalar(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 执行一条SQL语句,返回第一行第一列的值,如果值为空以whenNUll值取代
        /// </summary>
        public string ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] cmdParas, string whenNull)
        {
            object obj = ExecuteScalar(cmdType, cmdText, cmdParas);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                return whenNull;
            else
                return Convert.ToString(obj);
        }

        /// <summary>
        /// 执行一个带参数的SQL语句,返回一个DataReader对象
        /// </summary>
        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, SqlParameter[] cmdParas)
        {
            try
            {
                SqlCommand Cmd = new SqlCommand();
                CreateSqlCommand(Cmd, cmdType, cmdText, cmdParas);
                Cmd.CommandTimeout = 120;
                return Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                throw ex;
            } 
        }

        /// <summary>
        /// 执行一条SQL语句,返回一个DataReader对象
        /// </summary>
        public SqlDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(CommandType.Text, cmdText, null);
        }


        /// <summary>
        /// 得到数据条数
        /// </summary>
        public int GetCount(string tbname, string condition)
        {
            string strSql = "select count(*) from " + tbname;
            if (condition != string.Empty)
                strSql += " where " + condition;
            return this.GetInt(ExecuteScalar(CommandType.Text, strSql, null));
        }



        /// <summary>
        ///  得到分组统计查询结果的行数
        /// </summary>
        /// <param name="feil">子查询的字段</param>
        /// <param name="tbname">子查询的表名</param>
        /// <param name="condition">子查询的条件</param>
        /// <returns></returns>
        public int GetCount(string feil, string tbname, string condition)
        {

            string strSql = "select count(*) from (";
            strSql += "select " + feil + " from " + tbname + "";
            if (condition != string.Empty) strSql += " where " + condition;
            strSql += ")as tableTemp";
            return this.GetInt(ExecuteScalar(CommandType.Text, strSql, null));
        }


        /// <summary>
        /// 获取分页SQL语句
        /// </summary>
        /// <param name="field">要查找出来的字段</param>
        /// <param name="condition">不带Where的条件</param>
        /// <param name="pageSize">显示条数</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="tbNames">表名/联合查询</param>
        /// <param name="sortNames">排序字段，注意标识放最后一位以，搁开</param>
        /// <param name="sortType">排序类型true：asc  false: desc</param>
        /// <returns></returns>
        private string GetPageSql(string field,string condition, int pageSize, int pageIndex, string tbNames, string sortNames, bool sortType)
        {
            string tbname, tbsortname, type;
            type = sortType ? "" : "DESC";
            tbname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ")" + " " : tbNames + " ";
            tbsortname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ") " : tbNames + " ";
            if (pageIndex == 1)
            {
                return "select top " + pageSize.ToString() + " "  + field + " from " + tbname + (!string.IsNullOrEmpty(condition) ? " where " + condition : string.Empty) + " order by " + sortNames + " " + type;
            }
            else
            {
                System.Text.StringBuilder PageSql = new System.Text.StringBuilder();
                PageSql.AppendFormat("Select top {0}  " + field + " from ", pageSize);
                PageSql.AppendFormat("{0}", tbname);
                PageSql.AppendFormat(" where {0} not in(select top {1} {0}",
                    sortNames.Substring(sortNames.LastIndexOf(",") + 1, sortNames.Length - sortNames.LastIndexOf(",") - 1),
                    pageSize * (pageIndex - 1));
                PageSql.AppendFormat(" from {0}", tbsortname);
                if (!string.IsNullOrEmpty(condition))
                {
                    PageSql.AppendFormat(" where {0} order by {1} {2}) and {0}", condition, sortNames, type);
                }
                else
                {
                    PageSql.AppendFormat(" order by {0} {1})", sortNames, type);
                }
                PageSql.AppendFormat(" order by {0} {1}", sortNames, type);
                return PageSql.ToString();
            }
        }

        /// <summary>
        /// 执行分页SQL语句，返回SqlDataReader
        /// </summary>
        /// <param name="field">要查找出来的字段</param>
        /// <param name="condition">不带Where的条件</param>
        /// <param name="pageSize">显示条数</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="tbNames">表名/联合查询</param>
        /// <param name="sortNames">排序字段，注意标识放最后一位以，搁开</param>
        /// <param name="sortType">排序类型true：asc  false: desc</param>
        /// <returns>返回SqlDataReader</returns>
        public SqlDataReader GetPageList(string field,string condition, int pageSize, int pageIndex, string tbNames, string sortNames, bool sortType)
        {
            string PageSql = GetPageSql(field,condition, pageSize, pageIndex, tbNames, sortNames, sortType);
            if (PageSql.Length >= 8000) throw new Exception("查询语句超过8000字符，请缩减！");
            return this.ExecuteReader(CommandType.Text, PageSql, null);
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            try
            {
                if (object.Equals(_conn, null))
                {
                    _conn = new SqlConnection(_connStr);
                    _conn.Open();
                }
                _tran = _conn.BeginTransaction(IsolationLevel.ReadCommitted);
                _tranFlag = true;
            }
            catch (SqlException ex)
            {
                _tranFlag = false;
                CloseDB();
                throw ex;
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (object.Equals(_tran, null))
            {
                return;
            }
            _tran.Commit();
            _tranFlag = false;
            CloseDB();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (object.Equals(_tran, null))
            {
                return;
            }
            _tran.Rollback();
            _tranFlag = false;
            CloseDB();
        }
        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            if (!object.Equals(_conn, null))
            {
                _conn.Close();
            }
            if (!object.Equals(_tran, null))
            {
                _tran.Dispose();
            }
        }

        #endregion
        #region "由Object取值"
        /// <summary>
        /// 取得Int
        /// </summary>
        public int GetInt(object obj)
        {
            if (object.Equals(obj, DBNull.Value))
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public int? GetNullableInt(object obj)
        {
            if (object.Equals(obj, DBNull.Value))
                return null;
            return Convert.ToInt32(obj);
        }


        /// <summary>
        /// 取得Short/Int16
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Int16 GetInt16(Object obj)
        {
            if (object.Equals(obj, DBNull.Value))
            {
                return 0;
            }
            else
            {
                return Convert.ToInt16(obj);
            }
        }
        /// <summary>
        /// 取得uint32
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public uint GetUint(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return 0; else return Convert.ToUInt32(obj);
        }
        /// <summary>
        /// 取得Uint64
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public ulong GetUlong(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return 0; else return Convert.ToUInt64(obj);
        }
        /// <summary>
        /// 取得Decimal
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Decimal GetDecimal(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return 0; else return Convert.ToDecimal(obj);
        }
        /// <summary>
        /// 取得Double
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Double GetDouble(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return 0; else return Convert.ToDouble(obj);
        }
        /// <summary>
        /// 取得Single
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Single GetSingle(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return 0; else return Convert.ToSingle(obj);
        }
        ///<summary>
        ///取得日期
        ///</summary>
        ///<param name="obj"></param>
        ///<returns></returns>
        public DateTime GetDateTime(object obj)
        {
            if (object.Equals(obj, DBNull.Value))
            {
                return DateTime.MinValue;
            }
            else
            {
                DateTime tmp;
                if (DateTime.TryParse(obj.ToString(), out tmp))
                {
                    return tmp;
                }
                else
                {
                    return DateTime.MinValue;
                }
            }
        }
        /// <summary>
        /// 取得boolean
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool GetBool(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return false; else return Convert.ToBoolean(obj);
        }
        /// <summary>
        /// 取得String
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public string GetString(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return string.Empty; else return obj.ToString();
        }

        public DateTime? GetNullDateTime(object obj)
        {
            if (object.Equals(obj, DBNull.Value)) return null; else return Convert.ToDateTime(obj);
        }
        #endregion

        #region 解密数据库连接字符串

        /// <summary>
        /// 解密数据库连接字符密码,重新生成连接字符串(需保证 密码连接字符字段为: pwd=)
        /// </summary>
        /// <param name="strConn"></param>
        /// <returns></returns>
        public string FindConnPwd(string strConn)
        {
            string strDecryptConn = "";
            string strPwd = "";
            string strPwdName = "pwd=";
            string[] strTemp = strConn.Split(';');
            for (int i = 0; i < strTemp.Length; i++)
            {
                int t = strTemp[i].Trim().IndexOf(strPwdName);
                if (t > -1)
                {
                    strPwd = Decrypt(strTemp[i].Trim().Substring(strPwdName.Length));
                    strTemp[i] = strPwdName + strPwd;
                }
                if (strDecryptConn == "") strDecryptConn = strTemp[i];
                else strDecryptConn += ";" + strTemp[i];
            }

            return strDecryptConn;
        }

        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Decrypt(string Text)
        {
            return Decrypt(Text, "MATICSOFT");
        }
        /// <summary> 
        /// 解密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string Decrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            int len;
            len = Text.Length / 2;
            byte[] inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
                i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }

        #endregion 
    }
}
